package com.mavenq.fly.utils.excelv3;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.Closeable;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 完整的 Excel 工具类（生产环境可用）
 * 支持导入导出，列互换，样式自定义等高级功能
 */
public class ExcelUtil {

    private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    private static final int DEFAULT_COLUMN_WIDTH = 15;
    private static final int MIN_COLUMN_WIDTH = 8;
    private static final int MAX_COLUMN_WIDTH = 50;
    private static final short DEFAULT_ROW_HEIGHT = 400;
    private static final short HEADER_ROW_HEIGHT = 500;

    // ==================== 导出相关方法 ====================

    /**
     * 基础导出
     */
    public static <T> void exportToExcel(List<T> dataList, String[] headers, String[] fieldNames,
                                         String sheetName, OutputStream outputStream, boolean isXlsx) {
        Workbook workbook = null;
        try {
            workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
            Sheet sheet = workbook.createSheet(sheetName != null ? sheetName : "Sheet1");

            // 设置默认列宽
            setDefaultColumnWidth(sheet, headers.length);

            // 创建样式
            CellStyle headerStyle = createHeaderStyle(workbook);
            CellStyle dataStyle = createDataStyle(workbook);

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.setHeight(HEADER_ROW_HEIGHT);

            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }

            // 填充数据
            if (dataList != null && !dataList.isEmpty()) {
                int rowNum = 1;
                for (T data : dataList) {
                    Row row = sheet.createRow(rowNum++);
                    row.setHeight(DEFAULT_ROW_HEIGHT);
                    fillRowData(row, data, fieldNames, dataStyle);
                }
            }

            // 自适应列宽
            autoSizeColumns(sheet, headers.length, dataList, fieldNames);

            workbook.write(outputStream);

        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败", e);
        } finally {
            closeWorkbook(workbook);
        }
    }

    /**
     * 高级导出
     */
    public static <T> void exportToExcelAdvanced(List<T> dataList, String[] headers, String[] fieldNames,
                                                 String sheetName, OutputStream outputStream,
                                                 boolean isXlsx, ExcelExportConfig config) {
        Workbook workbook = null;
        try {
            workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
            Sheet sheet = workbook.createSheet(sheetName != null ? sheetName : "Sheet1");

            // 设置默认列宽
            setDefaultColumnWidth(sheet, headers.length, config.getDefaultColumnWidth());

            // 创建样式
            CellStyle headerStyle = createCustomHeaderStyle(workbook, config);
            CellStyle dataStyle = createCustomDataStyle(workbook, config);

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.setHeight(config.getHeaderRowHeight());

            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }

            // 填充数据
            if (dataList != null && !dataList.isEmpty()) {
                int rowNum = 1;
                for (T data : dataList) {
                    Row row = sheet.createRow(rowNum++);
                    row.setHeight(config.getDataRowHeight());
                    fillRowData(row, data, fieldNames, dataStyle);
                }
            }

            // 根据配置决定是否自适应列宽
            if (config.isAutoSizeColumns()) {
                autoSizeColumns(sheet, headers.length, dataList, fieldNames,
                        config.getMinColumnWidth(), config.getMaxColumnWidth());
            }

            workbook.write(outputStream);

        } catch (Exception e) {
            throw new RuntimeException("导出Excel失败", e);
        } finally {
            closeWorkbook(workbook);
        }
    }

    // ==================== 导入相关方法 ====================

    /**
     * 基础导入
     */
    public static <T> List<T> importFromExcel(InputStream inputStream, String[] headers,
                                              String[] fieldNames, Class<T> clazz, boolean isXlsx) {
        ExcelImportConfig<T> config = new ExcelImportConfig<>(clazz)
                .expectedHeaders(headers);

        // 建立列映射
        for (int i = 0; i < headers.length && i < fieldNames.length; i++) {
            config.columnMapping(headers[i], fieldNames[i]);
        }

        return importExcel(inputStream, config, isXlsx);
    }

    /**
     * 智能导入Excel（支持列互换）
     */
    public static <T> List<T> importExcel(InputStream inputStream, ExcelImportConfig<T> config,
                                          boolean isXlsx) {
        List<T> resultList = new ArrayList<>();
        Workbook workbook = null;
        int successCount = 0;
        int errorCount = 0;

        try {
            workbook = isXlsx ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            if (sheet == null) {
                throw new RuntimeException("Excel文件中没有工作表");
            }

            // 解析表头并建立列映射
            Map<Integer, String> columnFieldMap = parseHeaderAndCreateMapping(sheet, config);

            if (columnFieldMap.isEmpty()) {
                throw new RuntimeException("无法建立列映射关系，请检查表头配置");
            }

            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null || isEmptyRow(row)) continue;

                try {
                    T obj = parseRowToObject(row, columnFieldMap, config);
                    if (obj != null) {
                        resultList.add(obj);
                        successCount++;

                        // 调用导入监听器
                        if (config.getImportListener() != null) {
                            config.getImportListener().onRowImported(obj, i);
                        }
                    }
                } catch (Exception e) {
                    errorCount++;
                    // 行解析错误处理
                    if (config.getImportListener() != null) {
                        config.getImportListener().onRowError(i, e);
                    } else {
                        System.err.println("第 " + (i + 1) + " 行解析失败: " + e.getMessage());
                    }
                }
            }

            // 导入完成回调
            if (config.getImportListener() != null) {
                config.getImportListener().onComplete(successCount, errorCount);
            }

        } catch (Exception e) {
            throw new RuntimeException("导入Excel失败: " + e.getMessage(), e);
        } finally {
            closeWorkbook(workbook);
            closeStream(inputStream);
        }

        return resultList;
    }

    /**
     * 增强导入方法 - 返回包含详细错误信息的结果对象
     */
    public static <T> ImportResult<T> importExcelWithValidation(InputStream inputStream,
                                                                ExcelImportConfig<T> config,
                                                                boolean isXlsx) {
        List<T> successData = new ArrayList<>();
        List<ImportErrorDetail> errors = new ArrayList<>();
        Workbook workbook = null;
        String sheetName = "";
        int totalRows = 0;

        try {
            workbook = isXlsx ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            sheetName = sheet.getSheetName();
            totalRows = sheet.getLastRowNum();

            // 解析表头并建立列映射
            Map<Integer, String> columnFieldMap = parseHeaderAndCreateMapping(sheet, config);

            if (columnFieldMap.isEmpty()) {
                errors.add(new ImportErrorDetail(0, 0, sheetName, "", "",
                        "无法建立列映射关系，请检查表头配置", ImportErrorDetail.ErrorType.DATA_FORMAT_ERROR));
                return new ImportResult<>(successData, errors, totalRows, 0, errors.size());
            }

            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null || isEmptyRow(row)) continue;

                List<ImportErrorDetail> rowErrors = new ArrayList<>();
                T obj = null;

                try {
                    // 解析行数据
                    obj = parseRowToObject(row, columnFieldMap, config, sheetName, rowErrors);

                    if (obj != null && rowErrors.isEmpty()) {
                        // 执行业务校验
                        performBusinessValidation(obj, i, config, rowErrors);

                        if (rowErrors.isEmpty()) {
                            successData.add(obj);
                            // 调用导入监听器
                            if (config.getImportListener() != null) {
                                config.getImportListener().onRowImported(obj, i);
                            }
                        } else {
                            errors.addAll(rowErrors);
                        }
                    } else {
                        errors.addAll(rowErrors);
                    }

                } catch (Exception e) {
                    // 捕获解析过程中的异常
                    errors.add(new ImportErrorDetail(i, 0, sheetName, "", "",
                            "行解析异常: " + e.getMessage(), ImportErrorDetail.ErrorType.DATA_FORMAT_ERROR));
                }
            }

            // 导入完成回调
            if (config.getImportListener() != null) {
                config.getImportListener().onComplete(successData.size(), errors.size());
            }

        } catch (Exception e) {
            errors.add(new ImportErrorDetail(0, 0, sheetName, "", "",
                    "导入过程异常: " + e.getMessage(), ImportErrorDetail.ErrorType.DATA_FORMAT_ERROR));
        } finally {
            closeWorkbook(workbook);
            closeStream(inputStream);
        }

        return new ImportResult<>(successData, errors, totalRows, successData.size(), errors.size());
    }

    /**
     * 解析行数据到对象（包含字段级校验）
     */
    private static <T> T parseRowToObject(Row row, Map<Integer, String> columnFieldMap,
                                          ExcelImportConfig<T> config, String sheetName,
                                          List<ImportErrorDetail> errors) throws Exception {
        T obj = config.getTargetClass().newInstance();
        boolean hasData = false;

        for (Map.Entry<Integer, String> entry : columnFieldMap.entrySet()) {
            int columnIndex = entry.getKey();
            String fieldName = entry.getValue();

            Cell cell = row.getCell(columnIndex);
            Object cellValue = getCellValue(cell, config);

            // 必填字段校验
            if (config.getRequiredFields().contains(fieldName) && isEmptyValue(cellValue)) {
                errors.add(new ImportErrorDetail(row.getRowNum(), columnIndex, sheetName,
                        fieldName, cellValue, "该字段为必填字段", ImportErrorDetail.ErrorType.REQUIRED_ERROR));
                continue;
            }

            if (cellValue != null) {
                try {
                    // 字段级校验
                    List<String> fieldErrors = performFieldValidation(cellValue, fieldName,
                            row.getRowNum(), columnIndex, config);

                    if (!fieldErrors.isEmpty()) {
                        for (String errorMsg : fieldErrors) {
                            errors.add(new ImportErrorDetail(row.getRowNum(), columnIndex, sheetName,
                                    fieldName, cellValue, errorMsg, ImportErrorDetail.ErrorType.VALIDATION_ERROR));
                        }
                        continue;
                    }

                    // 设置字段值
                    setFieldValue(obj, fieldName, cellValue, config, row.getRowNum(), columnIndex, sheetName);
                    hasData = true;

                } catch (ExcelImportException e) {
                    errors.add(new ImportErrorDetail(row.getRowNum(), columnIndex, sheetName,
                            fieldName, cellValue, e.getMessage(), ImportErrorDetail.ErrorType.DATA_FORMAT_ERROR));
                } catch (Exception e) {
                    errors.add(new ImportErrorDetail(row.getRowNum(), columnIndex, sheetName,
                            fieldName, cellValue, "字段值设置失败: " + e.getMessage(),
                            ImportErrorDetail.ErrorType.DATA_FORMAT_ERROR));
                }
            }
        }

        return hasData ? obj : null;
    }

    /**
     * 执行业务级校验
     */
    private static <T> void performBusinessValidation(T obj, int rowIndex,
                                                      ExcelImportConfig<T> config,
                                                      List<ImportErrorDetail> errors) {
        for (BusinessValidator<T> validator : config.getBusinessValidators()) {
            List<ImportErrorDetail> businessErrors = validator.validate(obj, rowIndex);
            if (businessErrors != null) {
                errors.addAll(businessErrors);
            }
        }
    }

    /**
     * 检查是否为空值
     */
    private static boolean isEmptyValue(Object value) {
        if (value == null) return true;
        if (value instanceof String) {
            return ((String) value).trim().isEmpty();
        }
        return false;
    }

    /**
     * 执行字段级校验
     */
    private static <T> List<String> performFieldValidation(Object value, String fieldName,
                                                           int rowIndex, int columnIndex,
                                                           ExcelImportConfig<T> config) {
        List<String> errors = new ArrayList<>();

        FieldValidator validator = config.getFieldValidators().get(fieldName);
        if (validator != null) {
            List<String> validationErrors = validator.validate(value, fieldName, rowIndex, columnIndex);
            if (validationErrors != null) {
                errors.addAll(validationErrors);
            }
        }

        return errors;
    }

    // ==================== 私有工具方法 ====================

    private static void setDefaultColumnWidth(Sheet sheet, int columnCount) {
        setDefaultColumnWidth(sheet, columnCount, DEFAULT_COLUMN_WIDTH);
    }

    private static void setDefaultColumnWidth(Sheet sheet, int columnCount, int width) {
        for (int i = 0; i < columnCount; i++) {
            sheet.setColumnWidth(i, width * 256);
        }
    }

    private static <T> void autoSizeColumns(Sheet sheet, int columnCount, List<T> dataList, String[] fieldNames) {
        autoSizeColumns(sheet, columnCount, dataList, fieldNames, MIN_COLUMN_WIDTH, MAX_COLUMN_WIDTH);
    }

    private static <T> void autoSizeColumns(Sheet sheet, int columnCount, List<T> dataList,
                                            String[] fieldNames, int minWidth, int maxWidth) {
        for (int i = 0; i < columnCount; i++) {
            sheet.autoSizeColumn(i);

            int currentWidth = sheet.getColumnWidth(i);
            int charWidth = currentWidth / 256;

            if (charWidth < minWidth) {
                sheet.setColumnWidth(i, minWidth * 256);
            } else if (charWidth > maxWidth) {
                sheet.setColumnWidth(i, maxWidth * 256);
            }

            int adjustedWidth = sheet.getColumnWidth(i);
            sheet.setColumnWidth(i, adjustedWidth + 512);
        }
    }

    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        style.setBorderBottom(BorderStyle.MEDIUM);
        style.setBorderTop(BorderStyle.MEDIUM);
        style.setBorderLeft(BorderStyle.MEDIUM);
        style.setBorderRight(BorderStyle.MEDIUM);

        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 14);
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);

        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);

        return style;
    }

    private static CellStyle createDataStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();

        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);

        DataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));

        return style;
    }

    private static CellStyle createCustomHeaderStyle(Workbook workbook, ExcelExportConfig config) {
        CellStyle style = workbook.createCellStyle();

        if (config.getHeaderBgColor() != null) {
            style.setFillForegroundColor(config.getHeaderBgColor().getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        style.setBorderBottom(config.getHeaderBorderStyle());
        style.setBorderTop(config.getHeaderBorderStyle());
        style.setBorderLeft(config.getHeaderBorderStyle());
        style.setBorderRight(config.getHeaderBorderStyle());

        Font font = workbook.createFont();
        font.setBold(config.isHeaderBold());
        font.setFontHeightInPoints(config.getHeaderFontSize());
        if (config.getHeaderFontColor() != null) {
            font.setColor(config.getHeaderFontColor().getIndex());
        }
        style.setFont(font);

        style.setAlignment(config.getHeaderAlignment());
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(config.isWrapText());

        return style;
    }

    private static CellStyle createCustomDataStyle(Workbook workbook, ExcelExportConfig config) {
        CellStyle style = workbook.createCellStyle();

        style.setBorderBottom(config.getDataBorderStyle());
        style.setBorderTop(config.getDataBorderStyle());
        style.setBorderLeft(config.getDataBorderStyle());
        style.setBorderRight(config.getDataBorderStyle());

        style.setVerticalAlignment(VerticalAlignment.CENTER);

        if (config.getDataAlignment() != null) {
            style.setAlignment(config.getDataAlignment());
        }

        style.setWrapText(config.isWrapText());

        return style;
    }

    private static <T> void fillRowData(Row row, T data, String[] fieldNames, CellStyle style) {
        try {
            for (int i = 0; i < fieldNames.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(style);

                String fieldName = fieldNames[i];
                Object value = getFieldValue(data, fieldName);
                setCellValue(cell, value);
            }
        } catch (Exception e) {
            throw new RuntimeException("填充行数据失败", e);
        }
    }

    private static Object getFieldValue(Object obj, String fieldName) {
        try {
            Field field = getField(obj.getClass(), fieldName);
            if (field == null) return "";

            field.setAccessible(true);
            return field.get(obj);
        } catch (Exception e) {
            return "";
        }
    }

    private static void setCellValue(Cell cell, Object value) {
        if (value == null) {
            cell.setCellValue("");
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            if (value instanceof Integer || value instanceof Long ||
                    value instanceof Double || value instanceof Float) {
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) value).doubleValue());
            }
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else {
            cell.setCellValue(value.toString());
        }
    }

    private static <T> Map<Integer, String> parseHeaderAndCreateMapping(Sheet sheet, ExcelImportConfig<T> config) {
        Map<Integer, String> columnFieldMap = new HashMap<>();
        Row headerRow = sheet.getRow(0);

        if (headerRow == null) {
            throw new RuntimeException("Excel文件缺少表头行");
        }

        List<String> actualHeaders = new ArrayList<>();
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            Cell cell = headerRow.getCell(i);
            String headerName = getCellValueAsString(cell);
            actualHeaders.add(headerName);
        }

        if (config.getExpectedHeaders() != null && config.getExpectedHeaders().length > 0) {
            validateHeaders(actualHeaders, config);
        }

        if (!config.getIndexMapping().isEmpty()) {
            columnFieldMap.putAll(config.getIndexMapping());
        } else if (!config.getColumnMapping().isEmpty()) {
            for (int i = 0; i < actualHeaders.size(); i++) {
                String headerName = actualHeaders.get(i);
                String fieldName = config.getColumnMapping().get(headerName);
                if (fieldName != null) {
                    columnFieldMap.put(i, fieldName);
                }
            }
        } else {
            for (int i = 0; i < actualHeaders.size(); i++) {
                String headerName = actualHeaders.get(i);
                if (headerName != null && !headerName.trim().isEmpty()) {
                    String fieldName = toCamelCase(headerName.trim());
                    columnFieldMap.put(i, fieldName);
                }
            }
        }

        return columnFieldMap;
    }

    private static <T> void validateHeaders(List<String> actualHeaders, ExcelImportConfig<T> config) {
        String[] expectedHeaders = config.getExpectedHeaders();

        if (config.isStrictHeaderCheck()) {
            if (actualHeaders.size() != expectedHeaders.length) {
                throw new RuntimeException(
                        String.format("表头数量不匹配，期望: %d，实际: %d",
                                expectedHeaders.length, actualHeaders.size()));
            }

            for (int i = 0; i < expectedHeaders.length; i++) {
                String expected = expectedHeaders[i];
                String actual = i < actualHeaders.size() ? actualHeaders.get(i) : "";
                if (!expected.equals(actual)) {
                    throw new RuntimeException(
                            String.format("第 %d 列表头不匹配，期望: '%s'，实际: '%s'",
                                    i + 1, expected, actual));
                }
            }
        } else {
            Set<String> actualHeaderSet = new HashSet<>(actualHeaders);
            for (String expected : expectedHeaders) {
                if (!actualHeaderSet.contains(expected)) {
                    throw new RuntimeException("缺少必要表头: " + expected);
                }
            }
        }
    }

    private static <T> T parseRowToObject(Row row, Map<Integer, String> columnFieldMap, ExcelImportConfig<T> config) throws Exception {
        T obj = config.getTargetClass().newInstance();
        boolean hasData = false;

        for (Map.Entry<Integer, String> entry : columnFieldMap.entrySet()) {
            int columnIndex = entry.getKey();
            String fieldName = entry.getValue();

            Cell cell = row.getCell(columnIndex);
            Object cellValue = getCellValue(cell, config);

            if (cellValue != null) {
                setFieldValue(obj, fieldName, cellValue, config);
                hasData = true;
            }
        }

        return hasData ? obj : null;
    }

    private static Object getCellValue(Cell cell, ExcelImportConfig<?> config) {
        if (cell == null) return null;

        Object value;
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getStringCellValue();
                if (config.isTrimCellValue() && value != null) {
                    value = value.toString().trim();
                }
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case FORMULA:
                try {
                    value = cell.getNumericCellValue();
                } catch (Exception e) {
                    try {
                        value = cell.getStringCellValue();
                    } catch (Exception e2) {
                        value = cell.getCellFormula();
                    }
                }
                break;
            case BLANK:
                value = null;
                break;
            default:
                value = null;
        }

        return value;
    }

    private static <T> void setFieldValue(T obj, String fieldName, Object value, ExcelImportConfig<T> config) {
        try {
            Field field = getField(obj.getClass(), fieldName);
            if (field == null) return;

            field.setAccessible(true);
            Class<?> fieldType = field.getType();

            CellValueConverter converter = config.getCustomConverters().get(fieldName);
            if (converter != null) {
                value = converter.convert(value, fieldType);
            } else {
                value = convertValue(value, fieldType, config);
            }

            field.set(obj, value);

        } catch (Exception e) {
            throw new RuntimeException(
                    String.format("设置字段 %s 值失败: %s", fieldName, e.getMessage()), e);
        }
    }

    /**
     * 设置字段值（增强错误处理）
     */
    private static <T> void setFieldValue(T obj, String fieldName, Object value, ExcelImportConfig<T> config,
                                          int rowIndex, int columnIndex, String sheetName) {
        try {
            Field field = getField(obj.getClass(), fieldName);
            if (field == null) {
                throw new ExcelImportException("字段不存在", rowIndex, columnIndex,
                        sheetName, fieldName, value);
            }

            field.setAccessible(true);
            Class<?> fieldType = field.getType();

            // 使用自定义转换器
            CellValueConverter converter = config.getCustomConverters().get(fieldName);
            if (converter != null) {
                value = converter.convert(value, fieldType);
            } else {
                value = convertValue(value, fieldType, config, rowIndex, columnIndex, sheetName, fieldName);
            }

            field.set(obj, value);

        } catch (ExcelImportException e) {
            throw e;
        } catch (Exception e) {
            throw new ExcelImportException("字段值设置失败: " + e.getMessage(),
                    rowIndex, columnIndex, sheetName, fieldName, value, e);
        }
    }

    private static Object convertValue(Object value, Class<?> targetType,
                                       ExcelImportConfig<?> config) {
        if (value == null) return null;

        try {
            if (targetType == String.class) {
                return value.toString();
            } else if (targetType == Integer.class || targetType == int.class) {
                if (value instanceof Number) {
                    return ((Number) value).intValue();
                } else {
                    return Integer.parseInt(value.toString().trim());
                }
            } else if (targetType == Long.class || targetType == long.class) {
                if (value instanceof Number) {
                    return ((Number) value).longValue();
                } else {
                    return Long.parseLong(value.toString().trim());
                }
            } else if (targetType == Double.class || targetType == double.class) {
                if (value instanceof Number) {
                    return ((Number) value).doubleValue();
                } else {
                    return Double.parseDouble(value.toString().trim());
                }
            } else if (targetType == BigDecimal.class) {
                return new BigDecimal(value.toString().trim());
            } else if (targetType == Date.class) {
                if (value instanceof Date) {
                    return value;
                } else {
                    SimpleDateFormat sdf = new SimpleDateFormat(config.getDateFormat());
                    return sdf.parse(value.toString());
                }
            } else if (targetType == Boolean.class || targetType == boolean.class) {
                if (value instanceof Boolean) {
                    return value;
                } else {
                    String strValue = value.toString().toLowerCase();
                    return "true".equals(strValue) || "1".equals(strValue) || "是".equals(strValue);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(
                    String.format("值转换失败: 值 '%s' 无法转换为 %s", value, targetType.getSimpleName()), e);
        }

        return value;
    }

    /**
     * 值类型转换（增强错误处理）
     */
    private static Object convertValue(Object value, Class<?> targetType,
                                       ExcelImportConfig<?> config,
                                       int rowIndex, int columnIndex, String sheetName, String fieldName) {
        if (value == null) return null;

        try {
            if (targetType == String.class) {
                return value.toString();
            } else if (targetType == Integer.class || targetType == int.class) {
                if (value instanceof Number) {
                    return ((Number) value).intValue();
                } else {
                    return Integer.parseInt(value.toString().trim());
                }
            } else if (targetType == Long.class || targetType == long.class) {
                if (value instanceof Number) {
                    return ((Number) value).longValue();
                } else {
                    return Long.parseLong(value.toString().trim());
                }
            } else if (targetType == Double.class || targetType == double.class) {
                if (value instanceof Number) {
                    return ((Number) value).doubleValue();
                } else {
                    return Double.parseDouble(value.toString().trim());
                }
            } else if (targetType == BigDecimal.class) {
                return new BigDecimal(value.toString().trim());
            } else if (targetType == Date.class) {
                if (value instanceof Date) {
                    return value;
                } else {
                    SimpleDateFormat sdf = new SimpleDateFormat(config.getDateFormat());
                    return sdf.parse(value.toString());
                }
            } else if (targetType == Boolean.class || targetType == boolean.class) {
                if (value instanceof Boolean) {
                    return value;
                } else {
                    String strValue = value.toString().toLowerCase();
                    return "true".equals(strValue) || "1".equals(strValue) || "是".equals(strValue);
                }
            }
        } catch (Exception e) {
            throw new ExcelImportException(
                    String.format("值转换失败: 值 '%s' 无法转换为 %s", value, targetType.getSimpleName()),
                    rowIndex, columnIndex, sheetName, fieldName, value, e);
        }

        return value;
    }

    private static String toCamelCase(String headerName) {
        String[] parts = headerName.split("[\\s_]+");
        StringBuilder result = new StringBuilder(parts[0].toLowerCase());

        for (int i = 1; i < parts.length; i++) {
            if (!parts[i].isEmpty()) {
                result.append(Character.toUpperCase(parts[i].charAt(0)))
                        .append(parts[i].substring(1).toLowerCase());
            }
        }

        return result.toString();
    }

    private static boolean isEmptyRow(Row row) {
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null) {
                Object value = getCellValueAsString(cell);
                if (value != null && !value.toString().trim().isEmpty()) {
                    return false;
                }
            }
        }
        return true;
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    private static Field getField(Class<?> clazz, String fieldName) {
        for (Class<?> superClass = clazz; superClass != Object.class; superClass = superClass.getSuperclass()) {
            try {
                return superClass.getDeclaredField(fieldName);
            } catch (NoSuchFieldException e) {
                // 继续在父类中查找
            }
        }
        return null;
    }

    private static void closeWorkbook(Workbook workbook) {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                // 忽略关闭异常
            }
        }
    }

    private static void closeStream(Closeable stream) {
        if (stream != null) {
            try {
                stream.close();
            } catch (IOException e) {
                // 忽略关闭异常
            }
        }
    }

    /**
     * 合并单元格
     */
    public static void mergeCells(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }
}